package com.libreaccess.reporting.beans;

import com.libreaccess.reporting.utils.JasperUtils;
import com.libreaccess.reporting.utils.Logger;
import com.libreaccess.reporting.utils.ReportUtil;
import com.libreaccess.reporting.utils.ReportingEnums;
import com.libredigital.reporting.schema.*;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import oracle.xdb.XMLType;
import org.jboss.resource.adapter.jdbc.WrappedConnection;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import javax.naming.NamingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service(value = "com.libreaccess.reporting.beans.ReportsDAO")
public class ReportsDAOImpl implements ReportsDAO {

	final Logger logger = new Logger(getClass());
	private final String errMessage = "Error during DB access. ";

	/*
	 * get connection
	 * @return Connection
	 * @throws NamingException
	 * @throws SQLException
	 */
	private Connection getConnection() throws NamingException, SQLException {
		return JasperUtils.getDataSource().getConnection();
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getAllReports()
	 * @return List<Report>
	 * @throws NamingException
	 * @throws SQLException 
	 */
	public List<Report> getAllReports() throws NamingException, SQLException {
		String query = "SELECT LR_RPTMAS_REPORT_MASTER_ID \"id\"," + " LR_RPTMAS_NAME \"name\", "
				+ " LR_RPTMAS_DESCRIPTION \"description\"," + " LR_RPTMAS_LR_RPSREF_ID \"status\"" + " FROM LIBRE_REPORT.LR_REPORT_MASTER"
				+ " WHERE LR_RPTMAS_LR_RPTREF_ID = 1 and LR_RPTMAS_LR_RPSREF_ID = 1";

		logger.debug("getAllReports.query=" + query);

		ArrayList<Report> c = new ArrayList<Report>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(Report.class);

			int rowNum = 0;
			while (orset.next()) {
				Report r = (Report) mapper.mapRow(orset, rowNum);
				c.add(r);
				rowNum++;
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return c;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getResourceByFullName()
	 * @param String fullName of parameter
	 * @return ReportResource
	 * @throws NamingException
	 * @throws SQLException
	 */
	public ReportResource getResourceByFullName(String fullName) throws NamingException, SQLException {
		String query = " SELECT LR_RPTRES_REPORT_RESOURCE_ID \"id\"" + ", LR_RPTRES_SUBREPORT_CONTENT \"subreportContent\""
				+ ", LR_RPTRES_CONTENT \"resourceContent\"" + ", LR_RPTRES_TYPE \"type\"" + ", LR_RPTRES_FULLNAME \"fullName\""
				+ " FROM LIBRE_REPORT.LR_REPORT_RESOURCES_MASTER" + " WHERE LR_RPTRES_FULLNAME = ?";

		logger.debug("getResourceByFullName.query=" + query);
		logger.debug("fullName=" + fullName);

		ReportResource r = null;
		Connection conn = null;
		OraclePreparedStatement stmt = null;
		ResultSet rset = null;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}
			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setString(1, fullName);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;
			RowMapper mapper = new BeanPropertyRowMapper(ReportResource.class);
			if (orset.next()) {
				r = (ReportResource) mapper.mapRow(orset, 0);
				if (orset.getOPAQUE("subreportContent") != null) {
					XMLType subreport = XMLType.createXML(orset.getOPAQUE("subreportContent"));
					r.setSubreportContent(subreport.getStringVal());
				}
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return r;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getById()
	 * @param Long id
	 * @return Report
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Report getById(Long id) throws NamingException, SQLException {
		String query = "select LR_RPTMAS_REPORT_MASTER_ID \"id\"," + " LR_RPTMAS_NAME \"name\", "
				+ " LR_RPTMAS_DESCRIPTION \"description\"," + " LR_RPTMAS_LR_RPSREF_ID \"status\"" + " from LIBRE_REPORT.LR_REPORT_MASTER"
				+ " where LR_RPTMAS_LR_RPTREF_ID = 1 " + " and LR_RPTMAS_REPORT_MASTER_ID = ? ";

		logger.debug("getById.query =" + query);
		logger.debug("Id =" + id);

		Report r = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			stmt.setLong(1, id);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(Report.class);

			if (orset.next()) {
				r = (Report) mapper.mapRow(orset, 0);
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return r;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getAllViewsByReport()
	 * @param Long reportId
	 * @return List<ReportView>
	 * @throws NamingException
	 * @throws SQLException
	 */
	public List<ReportView> getAllViewsByReport(long reportId) throws NamingException, SQLException {
		String query = "select lrdvr.LR_RPTDET_VIEW_REF_ID \"id\"" + ", lrdvr.LR_RPTMAS_REPORT_MASTER_ID \"reportId\""
				+ ", lrdvr.LR_RPTDET_VIEW_NAME \"contentName\"" + ", lrdvr.LR_RPTVWS_REPORT_VIEW_ID \"viewId\""
				+ ", lrdvr.lr_rptdet_view_description \"contentDescription\"" + ", lrvm.lr_rptvws_view_description \"description\""
				+ ", lrvm.LR_RPTVWS_RPTMAS_NAME \"name\"" + ", lrdvr.LR_RPTDET_JRXML_CONTENT \"jrxml\"" + ", nvl(lrdvr.LR_RPTDS_DATASOURCE_ID,0) \"datasourceId\"" 
				+ " ,(select count(*) from"
				+ " (select * from LR_REPORT_DETAIL_VIEW_REF where LR_RPTDET_JRXML_CONTENT is not null)"

				+ " group by LR_RPTMAS_REPORT_MASTER_ID,LR_RPTVWS_REPORT_VIEW_ID"
				+ " having LR_RPTVWS_REPORT_VIEW_ID = lrdvr.LR_RPTVWS_REPORT_VIEW_ID"
				+ " and LR_RPTMAS_REPORT_MASTER_ID = lrdvr.LR_RPTMAS_REPORT_MASTER_ID ) \"maxViewNumber\""
				+ " from LR_REPORT_DETAIL_VIEW_REF lrdvr" + " inner join LR_REPORT_VIEWS_MASTER lrvm"
				+ " on lrvm.LR_RPTVWS_REPORT_VIEW_ID = lrdvr.LR_RPTVWS_REPORT_VIEW_ID"
//				+ " left join LR_REPORT_DATASOURCE_MASTER lrds" + " on lrds.LR_RPTDS_DATASOURCE_ID = lrdvr.LR_RPTDS_DATASOURCE_ID"
				+ " where lrdvr.LR_RPTMAS_REPORT_MASTER_ID = ?"
				+ " and lrdvr.LR_RPTDET_JRXML_CONTENT is not null";

		logger.debug("getAllViewsByReport query:" + query);
		logger.debug("reportId=" + reportId);

		ArrayList<ReportView> r = new ArrayList<ReportView>();
		Connection conn = null;
		OraclePreparedStatement stmt = null;
		ResultSet rset = null;
		ReportView rv;
		HashMap<Long, Long> contentName = new HashMap<Long, Long>();

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setLong(1, reportId);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(ReportView.class);

			int rowNum = 0;
			while (orset.next()) {
				rv = (ReportView) mapper.mapRow(orset, rowNum);

				// get the XMLType
				if (orset.getOPAQUE("jrxml") != null) {
					XMLType jrxml = XMLType.createXML(orset.getOPAQUE("jrxml"));
					rv.setJrxml(jrxml.getStringVal());
				}

				// get number view
				long currentNumber = contentName.containsKey(orset.getLong("viewId")) ? contentName.get(orset.getLong("viewId")) : 0;
				rv.setViewNumber(++currentNumber);

				if (contentName.containsKey(orset.getLong("viewId"))) {
					contentName.remove(orset.getLong("viewId"));
				}

				contentName.put(orset.getLong("viewId"), currentNumber);
				r.add(rv);
				rowNum++;
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return r;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getReportViewById()
	 * @param Long id
	 * @return List<ReportView>
	 * @throws NamingException
	 * @throws SQLException
	 */
	public ReportView getReportViewById(Long id) throws NamingException, SQLException {
		String query =

		"select lrdvr.LR_RPTDET_VIEW_REF_ID \"id\"" + ", lrdvr.LR_RPTMAS_REPORT_MASTER_ID \"reportId\""
				+ ", lrdvr.LR_RPTDET_VIEW_NAME \"contentName\"" + ", lrdvr.LR_RPTVWS_REPORT_VIEW_ID \"viewId\""
				+ ", lrdvr.lr_rptdet_view_description \"contentDescription\"" + ", lrvm.lr_rptvws_view_description \"description\""
				+ ", lrvm.LR_RPTVWS_RPTMAS_NAME \"name\"" + ", lrdvr.LR_RPTDET_JRXML_CONTENT \"jrxml\"" + ", nvl(lrdvr.LR_RPTDS_DATASOURCE_ID,0) \"datasourceId\"" 
				+ " ,(select count(*) from"
				+ " (select * from lr_report_detail_view_ref where LR_RPTDET_JRXML_CONTENT is not null)"
				+ " group by LR_RPTMAS_REPORT_MASTER_ID,LR_RPTVWS_REPORT_VIEW_ID"
				+ " having LR_RPTVWS_REPORT_VIEW_ID = lrdvr.LR_RPTVWS_REPORT_VIEW_ID"
				+ " and LR_RPTMAS_REPORT_MASTER_ID = lrdvr.LR_RPTMAS_REPORT_MASTER_ID ) \"maxViewNumber\""
				+ " from LR_REPORT_DETAIL_VIEW_REF lrdvr" + " inner join LR_REPORT_VIEWS_MASTER lrvm"
				+ " on lrvm.LR_RPTVWS_REPORT_VIEW_ID = lrdvr.LR_RPTVWS_REPORT_VIEW_ID" 
//				+ " left join LR_REPORT_DATASOURCE_MASTER lrds" + " on lrds.LR_RPTDS_DATASOURCE_ID = lrdvr.LR_RPTDS_DATASOURCE_ID"
				+ " where lrdvr.LR_RPTDET_VIEW_REF_ID = ?"
				+ " and lrdvr.LR_RPTDET_JRXML_CONTENT is not null";
		;

		logger.debug("getReportViewById query:" + query);
		logger.debug("Id=" + id);

		ReportView rv = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			stmt.setLong(1, id);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(ReportView.class);

			if (orset.next()) {
				rv = (ReportView) mapper.mapRow(orset, 0);
				if (orset.getOPAQUE("jrxml") != null) {
					XMLType jrxml = XMLType.createXML(orset.getOPAQUE("jrxml"));
					rv.setJrxml(jrxml.getStringVal());
				}
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return rv;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getAllReportViewFormats()
	 * @return List<OutputFormat>
	 * @throws NamingException
	 * @throws SQLException
	 */
	public List<OutputFormat> getAllReportViewFormats() throws NamingException, SQLException {
		String query = " select LR_FRMMAS_NAME \"name\", LR_FRMMAS_DESCRIPTION \"description\" from LR_REPORT_FORMATS_MASTER";

		logger.debug("getAllReportViewFormats query:" + query);

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		ArrayList<OutputFormat> frm = new ArrayList<OutputFormat>();

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			rset = stmt.executeQuery();
			ResultSet orset = rset;

			
			RowMapper mapper = new BeanPropertyRowMapper(OutputFormat.class);

			int rowNum = 0;
			while (orset.next()) {
				OutputFormat f = (OutputFormat) mapper.mapRow(orset, rowNum);
				f.setDescription(f.getDescription() != null ? f.getDescription():"");
				f.setName(f.getName().toUpperCase());
				frm.add(f);
				rowNum++;
			}
			
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return frm;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getReportViewFormatsByViewId()
	 * @param Long id
	 * @return List<OutputFormat>
	 * @throws NamingException
	 * @throws SQLException
	 */
	public List<OutputFormat> getReportViewFormatsByViewId(Long id) throws NamingException, SQLException {
		String query = " select LR_FRMMAS_NAME \"name\", LR_FRMMAS_DESCRIPTION  \"description\" from LR_REPORT_VIEW_FORMAT_XRF lrvfx" + " inner join LR_REPORT_FORMATS_MASTER lrfm"
				+ " on lrvfx.LR_FRMMAS_FORMAT_ID = lrfm.LR_FRMMAS_FORMAT_ID" + " where lrvfx.LR_RPTDET_VIEW_REF_ID = ?";

		System.out.println("getReportViewFormatsByViewId query:" + query);
		System.out.println("Id=" + id);

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		ArrayList<OutputFormat> frm = new ArrayList<OutputFormat>();

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			stmt.setLong(1, id);
			rset = stmt.executeQuery();
			//ResultSet orset = rset;

			RowMapper mapper = new BeanPropertyRowMapper(OutputFormat.class);
			
			int rowNum = 0;
			while (rset.next()) {
				OutputFormat f = (OutputFormat) mapper.mapRow(rset, rowNum);
				f.setDescription(f.getDescription() != null ? f.getDescription():"");
				f.setName(f.getName().toUpperCase());
				frm.add(f);
				rowNum++;
			}
			
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return frm.isEmpty() ? getAllReportViewFormats() : frm;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#setReportViewFormatsByViewId()
	 * @param Long id
	 * @param List<String> frms
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response setReportViewFormatsByViewId(long viewId, List<String> frms) throws NamingException, SQLException {
		Response response = new Response();
		String query1 = " DELETE FROM LR_REPORT_VIEW_FORMAT_XRF WHERE LR_RPTDET_VIEW_REF_ID = ?";

		String query2 = "INSERT INTO LR_REPORT_VIEW_FORMAT_XRF(LR_RPTDET_VIEW_REF_ID, LR_FRMMAS_FORMAT_ID) VALUES (?,"
				+ " (select LR_FRMMAS_FORMAT_ID from LR_REPORT_FORMATS_MASTER where LR_FRMMAS_NAME=?))";

		logger.debug("setLogMaster query1:" + query1);
		logger.debug("setLogMaster query2:" + query2);
		logger.debug("viewId=" + viewId);
		for (String f : frms) {
			logger.debug("frm=" + f);
		}

		Connection conn = null;
		Connection uconn = null;
		PreparedStatement stmt = null;

		try {
			conn = getConnection();
			uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			uconn.setAutoCommit(false);
			if (!uconn.getAutoCommit()) {
				stmt = uconn.prepareStatement(query1);
				stmt.setLong(1, viewId);
				stmt.executeQuery();

				for (String f : frms) {
					stmt = uconn.prepareStatement(query2);
					stmt.setLong(1, viewId);
					stmt.setString(2, f.toLowerCase());
					stmt.executeQuery();
				}
				uconn.commit();
				response.setIsSuccess(true);
			}
		} catch (SQLException e) {
			response.setIsSuccess(false);
			response.setError(errMessage);
			logger.error(errMessage+ e.getMessage());
			logger.debug("Starting rollback operations...");
			try {
				if (uconn != null) {
					uconn.rollback();
				}
			} catch (SQLException se2) {
				logger.error("SQLException:"+se2.getMessage());
			}
		} catch (Exception e) {
			response.setError(errMessage);
			response.setIsSuccess(false);
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#setLogMaster()
	 * @param Long viewId
	 * @param String userName
	 * @param String param
	 * @return Long
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Long setLogMaster(long viewId, String userName, String params) throws NamingException, SQLException {
		String query1 = "INSERT INTO LR_REPORT_LOG_MASTER(LR_RPTDET_VIEW_REF_ID, LR_LOGMAS_USER_NAME, LR_LOGMAS_REPORT_PARAMETERS) VALUES (?, ?, ?)";
		String query2 = "SELECT LR_REPORT_LOG_MASTER_SQ.CURRVAL from DUAL";

		logger.debug("setLogMaster query1:" + query1);
		logger.debug("setLogMaster query2:" + query2);
		logger.debug("viewId=" + viewId);
		logger.debug("userName=" + userName);
		logger.debug("parameters=" + params);

		Connection conn = null;
		Connection uconn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		Long id = 0L;

		try {
			conn = getConnection();
			uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			uconn.setAutoCommit(false);
			if (!uconn.getAutoCommit()) {
				stmt = uconn.prepareStatement(query1);
				stmt.setLong(1, viewId);
				stmt.setString(2, userName);
				stmt.setString(3, params);
				stmt.executeQuery();

				stmt = uconn.prepareStatement(query2);
				rset = stmt.executeQuery();
				if (rset.next()) {
					id = rset.getLong(1);
				}
				uconn.commit();
			}
		} catch (SQLException e) {
			logger.error(errMessage+ e.getMessage());
			logger.debug("Starting rollback operations...");
			try {
				if (uconn != null) {
					uconn.rollback();
				}
			} catch (SQLException se2) {
				logger.error("SQLException:"+e.getMessage());
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return id;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#setLogDetail()
	 * @param Long logId
	 * @param long resId
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response setLogDetail(long logId, long resId) throws NamingException, SQLException {
		Response response = new Response();

		String query = "INSERT INTO LR_REPORT_LOG_DETAIL(LR_LOGMAS_LOG_ID, LR_RPTRES_REPORT_RESOURCE_ID) VALUES (?,?)";

		logger.debug("setLogDetail query:" + query);
		logger.debug("logId=" + logId);
		logger.debug("resId=" + resId);

		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}
			uconn.setAutoCommit(false);
			stmt = uconn.prepareStatement(query);
			stmt.setLong(1, logId);
			stmt.setLong(2, resId);
			stmt.executeQuery();
			uconn.commit();
			response.setIsSuccess(true);
		} catch (Exception e) {
			response.setError(errMessage);
			response.setIsSuccess(false);
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#deleteReport()
	 * @param Long reportId
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response deleteReport(long reportId) throws NamingException, SQLException {
		Response response = new Response();

		String query = "UPDATE LIBRE_REPORT.LR_REPORT_MASTER  SET LR_RPTMAS_LR_RPSREF_ID = 3, LR_RPTMAS_DELETE_DATE_UTC = sysdate"
				+ " WHERE LR_RPTMAS_REPORT_MASTER_ID = ?";

		Connection conn = null;
		OraclePreparedStatement stmt = null;

		logger.debug("deleteReport query:" + query);
		logger.debug("repId=" + reportId);

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}
			uconn.setAutoCommit(false);
			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setLong(1, reportId);

			stmt.execute();
			uconn.commit();
			response.setIsSuccess(true);
		} catch (Exception e) {
			response.setError(errMessage);
			response.setIsSuccess(false);
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#addResource()
	 * @param String type
	 * @param String fullName
	 * @param byte[] content
	 * @param byte[] subreport
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response addResource(String type, String fullName, byte[] content, byte[] subreport) throws NamingException, SQLException {
		Response response = new Response();
		String query = "insert into LIBRE_REPORT.LR_REPORT_MASTER (LR_RPTRES_TYPE, LR_RPTRES_FULLNAME"
				+ (content==null ? ", LR_RPTRES_SUBREPORT_CONTENT":", LR_RPTRES_CONTENT")
				+ ") VALUES (?, ?, ?)";

		Connection conn = null;
		OraclePreparedStatement stmt = null;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}
			uconn.setAutoCommit(false);
			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setString(1, type);
			stmt.setString(2, fullName);
			
			if (content!=null)
			{
				stmt.setBLOB(3, new BLOB(null,content));
			}
			else
			{
				stmt.setObject(3, XMLType.createXML(uconn, subreport.toString()));
			}
			
			logger.debug("Add new Resource");
			/*
			 * logger.debug("r.getName():" + // r.getName());
			 * logger.debug("getDescription():" + r.getDescription()); //
			 * logger.debug("getJrxml():" + r.getJrxml()); // //
			 * stmt.execute();
			 * // //
			 */
			stmt.execute();
			uconn.commit();
			response.setIsSuccess(true);
		} catch (Exception e) {
			response.setError(errMessage);
			response.setIsSuccess(false);
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getReportDSById()
	 * @param Long id
	 * @return ReportDS
	 * @throws NamingException
	 * @throws SQLException
	 */
	public ReportDS getReportDSById(Long id) throws NamingException, SQLException {
		
		String query = "select LR_RPTDS_DATASOURCE_NAME \"name\"" + ", LR_RPTDS_JDBC_DRIVER \"jdbcDriver\"" 
		+ ", LR_RPTDS_JDBC_URL \"jdbcUrl\"" + ", LR_RPTDS_DS_USERNAME \"userName\"" 
		+ ", LR_RCTMAS_DS_PASSWORD \"password\"" + " from LR_REPORT_DATASOURCE_MASTER"
		+ " where LR_RPTDS_DATASOURCE_ID = ?";
		


		logger.debug("getReportDSById.query =" + query);
		logger.debug("Id =" + id);

		ReportDS ds = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = uconn.prepareStatement(query);
			stmt.setLong(1, id);
			rset = stmt.executeQuery();
//			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(ReportDS.class);

			if (rset.next()) {
				ds = (ReportDS) mapper.mapRow(rset, 0);
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return ds;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getAllMyReportsByUser()
	 * @param String userName
	 * @return List<MyReport>
	 * @throws NamingException
	 * @throws SQLException
	 */
	public List<MyReport> getAllMyReportsByUser(String userName)throws NamingException, SQLException
	{
		String query = 
			"SELECT LR_LRMRT_MYREPORT_ID \"id\", LR_LRMRT_NAME \"name\", LR_LRMRT_USER_NAME \"userName\""
			+ ", lrmy.LR_RPTDET_VIEW_REF_ID \"viewId\", LR_LRMRT_PARAMETERS \"parameters\""
			+", lrm.lr_rptmas_name \"reportName\" ,lrm.lr_rptmas_report_master_id \"reportId\" FROM LR_REPORT_MYREPORT lrmy"
			+" left join LR_REPORT_DETAIL_VIEW_REF lrdvr on lrdvr.LR_RPTDET_VIEW_REF_ID = lrmy.LR_RPTDET_VIEW_REF_ID"
			+" left join LR_REPORT_MASTER lrm on lrm.lr_rptmas_report_master_id = lrdvr.lr_rptmas_report_master_id"
			+" WHERE LR_LRMRT_USER_NAME = ?";

		logger.debug("getAllMyReportsByUser query:" + query);
		logger.debug("userName=" + userName);

		ArrayList<MyReport> r = new ArrayList<MyReport>();
		Connection conn = null;
		OraclePreparedStatement stmt = null;
		ResultSet rset = null;
		MyReport mr;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setString(1, userName);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(MyReport.class);

			int rowNum = 0;
			while (orset.next()) {
				mr = (MyReport) mapper.mapRow(orset, rowNum);
				r.add(getInnerReportData(mr));
				rowNum++;
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return r;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getMyReportById()
	 * @param long id
	 * @return MyReport
	 * @throws NamingException
	 * @throws SQLException
	 */
	public MyReport getMyReportById(long id)throws NamingException, SQLException
	{
		String query = 
			"SELECT LR_LRMRT_MYREPORT_ID \"id\", LR_LRMRT_NAME \"name\", LR_LRMRT_USER_NAME \"userName\""
			+ ", lrmy.LR_RPTDET_VIEW_REF_ID \"viewId\", LR_LRMRT_PARAMETERS \"parameters\""
			+", lrm.lr_rptmas_name \"reportName\" ,lrm.lr_rptmas_report_master_id \"reportId\" FROM LR_REPORT_MYREPORT lrmy"
			+" left join LR_REPORT_DETAIL_VIEW_REF lrdvr on lrdvr.LR_RPTDET_VIEW_REF_ID = lrmy.LR_RPTDET_VIEW_REF_ID"
			+" left join LR_REPORT_MASTER lrm on lrm.lr_rptmas_report_master_id = lrdvr.lr_rptmas_report_master_id"
			+" WHERE LR_LRMRT_MYREPORT_ID = ?";

		logger.debug("getAllMyReportsByUser query:" + query);
		logger.debug("id=" + id);

		Connection conn = null;
		OraclePreparedStatement stmt = null;
		ResultSet rset = null;
		MyReport mr = null;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setLong(1, id);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(MyReport.class);

			int rowNum = 0;
			if (orset.next()) {
				mr = (MyReport) mapper.mapRow(orset, rowNum);
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return getInnerReportData(mr);
	}
	
	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#getMyReportByNameAndLoggedUserId()
	 * @param String name
	 * @param String userName
	 * @return MyReport
	 * @throws NamingException
	 * @throws SQLException
	 */
	public MyReport getMyReportByNameAndLoggedUserId(String name, String userName)throws NamingException, SQLException
	{
		String query = 
			"SELECT LR_LRMRT_MYREPORT_ID \"id\", LR_LRMRT_NAME \"name\", LR_LRMRT_USER_NAME \"userName\""
			+ ", lrmy.LR_RPTDET_VIEW_REF_ID \"viewId\", LR_LRMRT_PARAMETERS \"parameters\""
			+", lrm.lr_rptmas_name \"reportName\" ,lrm.lr_rptmas_report_master_id \"reportId\" FROM LR_REPORT_MYREPORT lrmy"
			+" left join LR_REPORT_DETAIL_VIEW_REF lrdvr on lrdvr.LR_RPTDET_VIEW_REF_ID = lrmy.LR_RPTDET_VIEW_REF_ID"
			+" left join LR_REPORT_MASTER lrm on lrm.lr_rptmas_report_master_id = lrdvr.lr_rptmas_report_master_id"
			+" WHERE LR_LRMRT_USER_NAME = ? and LR_LRMRT_NAME = ?";

		logger.debug("getMyReportByNameAndLoggedUserId query:" + query);
		logger.debug("name=" + name);
		logger.debug("UserName=" + userName);

		Connection conn = null;
		OraclePreparedStatement stmt = null;
		ResultSet rset = null;
		MyReport mr = null;

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}

			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setString(1, userName);
			stmt.setString(2, name);
			rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			RowMapper mapper = new BeanPropertyRowMapper(MyReport.class);

			int rowNum = 0;
			if (orset.next()) {
				mr = (MyReport) mapper.mapRow(orset, rowNum);
			}
		} catch (Exception e) {
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

		return getInnerReportData(mr);
	}
	
	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#saveMyReport()
	 * @param String name
	 * @param String userName
	 * @param long viewId
	 * @param byte[] parameters
	 * @param long myReportId
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response saveMyReport(String name, String userName, long viewId, byte[] parameters, long myReportId) throws NamingException, SQLException {
		Response response = new Response();

		boolean isNameUnique = true;
		List<MyReport> rpts = getAllMyReportsByUser(userName);
		if (rpts != null)
		{
			for (MyReport rpt: rpts)
			{
				if (rpt.getName()!=null && rpt.getName().equals(name) && rpt.getId() != myReportId)
				{
					isNameUnique = false;
				}
			}
		}
		
		if (isNameUnique)
		{
			String query = (myReportId == 0l)
				? "INSERT INTO LR_REPORT_MYREPORT(LR_LRMRT_NAME, LR_LRMRT_USER_NAME, LR_RPTDET_VIEW_REF_ID, LR_LRMRT_PARAMETERS)"
			+ " VALUES(?,?,?,?)" : 
				"UPDATE LR_REPORT_MYREPORT SET LR_LRMRT_NAME=?, LR_LRMRT_USER_NAME=?, LR_RPTDET_VIEW_REF_ID=?, LR_LRMRT_PARAMETERS =?"
			+ " WHERE LR_LRMRT_MYREPORT_ID =?";

			logger.debug("saveMyReport query:" + query);
			logger.debug("name=" + name);
			logger.debug("userName=" + userName);
			logger.debug("viewId=" + viewId);
			logger.debug("params="+parameters);
			logger.debug("params size="+parameters.length);

			/*temporary */
			MyReport mr = new MyReport();
			mr.setParameters(parameters);
			
			ReportUtil ru = JasperUtils.GetReportUtilFromByteArray(mr.getParameters());
			if (ru != null)
			{
				logger.debug("Email:"+ru.getEmail());
			}
			/*end temporary*/
			
			Connection conn = null;
			OraclePreparedStatement stmt = null;

			try {
				conn = getConnection();
				Connection uconn = conn;
				if (conn instanceof WrappedConnection) {
					uconn = ((WrappedConnection) conn).getUnderlyingConnection();
				}
				uconn.setAutoCommit(false);
				stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
				stmt.setString(1, name);
				stmt.setString(2, userName);
				stmt.setLong(3, viewId);
				stmt.setBytes(4, parameters);
				if (myReportId != 0l)
				{
					stmt.setLong(5, myReportId);
					logger.debug("my report id="+myReportId);	
					logger.debug("Update myReport");
				}
				else
				{
					logger.debug("Save myReport");
				}

				stmt.execute();
				uconn.commit();
				response.setIsSuccess(true);
			} catch (Exception e) {
				response.setError(errMessage);
				response.setIsSuccess(false);
				logger.error(errMessage+ e.getMessage());
			} finally {
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}
		}
		else
		{
			response.setError("Name is not unique. ");
			response.setIsSuccess(false);
			logger.warn("Name is not unique for current user");
		}
		return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#updateMyReportByTrigger()
	 * @param long myReportId
	 * @param byte[] parameters
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response updateMyReportByTrigger(long myReportId, byte[] parameters) throws NamingException, SQLException {
		Response response = new Response();

			String query = "UPDATE LR_REPORT_MYREPORT SET LR_LRMRT_PARAMETERS =?"
			+ " WHERE LR_LRMRT_MYREPORT_ID =?";

			logger.debug("saveMyReport query:" + query);
			logger.debug("myReportId=" + myReportId);
			logger.debug("params="+parameters);
			logger.debug("params size="+parameters.length);

			Connection conn = null;
			OraclePreparedStatement stmt = null;

			try {
				conn = getConnection();
				Connection uconn = conn;
				if (conn instanceof WrappedConnection) {
					uconn = ((WrappedConnection) conn).getUnderlyingConnection();
				}
				uconn.setAutoCommit(false);
				stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
				stmt.setBytes(1, parameters);
				stmt.setLong(2, myReportId);

				stmt.execute();
				uconn.commit();
				response.setIsSuccess(true);
			} catch (Exception e) {
				response.setError(errMessage);
				response.setIsSuccess(false);
				logger.error(errMessage+ e.getMessage());
			} finally {
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}
			return response;
	}

	/*
	 * @see com.libreaccess.reporting.beans.ReportsDAO#deleteMyReport()
	 * @param long myReportId
	 * @return Response
	 * @throws NamingException
	 * @throws SQLException
	 */
	public Response deleteMyReport(long myReportId) throws NamingException, SQLException {
		Response response = new Response();

		String query = "DELETE FROM LR_REPORT_MYREPORT WHERE LR_LRMRT_MYREPORT_ID = ?";

		Connection conn = null;
		OraclePreparedStatement stmt = null;

		logger.debug("deleteMyReport query:" + query);
		logger.debug("myReportId=" + myReportId);

		try {
			conn = getConnection();
			Connection uconn = conn;
			if (conn instanceof WrappedConnection) {
				uconn = ((WrappedConnection) conn).getUnderlyingConnection();
			}
			uconn.setAutoCommit(false);
			stmt = (OraclePreparedStatement) uconn.prepareStatement(query);
			stmt.setLong(1, myReportId);

			stmt.execute();
			uconn.commit();
			response.setIsSuccess(true);
		} catch (Exception e) {
			response.setError(errMessage);
			response.setIsSuccess(false);
			logger.error(errMessage+ e.getMessage());
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return response;
	}

	/*
	 * @param MyReport rpt
	 * @return MyReport
	 */
	private MyReport getInnerReportData(MyReport rpt)
	{
        ReportUtil ru = null;
        if (rpt!=null)
		    ru = JasperUtils.GetReportUtilFromByteArray(rpt.getParameters());
		if (ru!=null) {
			rpt.setFormatName(ru.getReportFormat()!= null? ru.getReportFormat().toString(): ReportingEnums.Format.HTML.toString());
			if (ru.getSchedulerFrequency() != null && ru.getTriggerId() != null && ru.getSchedulerCountToRun()>0/* && Utils.isStringEmpty(ru.getSchedulerFrequency().toString())*/){
				switch(ru.getSchedulerFrequency()) {
				case OneTime:
					rpt.setScheduleType("One Time");
					break;
				case Daily:
				case Weekly:
				case Monthly:
				case Yearly:
					rpt.setScheduleType(ru.getSchedulerFrequency().toString());
					break;
				}
				
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				
				if (ru.getTriggerExpr() != null && ru.getTriggerExpr().getNextFireTime() != null){
					rpt.setScheduleNextRun(sdf.format(ru.getTriggerExpr().getNextFireTime()));
				} else {
					rpt.setScheduleNextRun("");	
					rpt.setScheduleType("");}
			}
		}
		
		return rpt;
	}

}